using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace ClienteCaptura.Core {

  public class Centro {
    
    private int id;
    private string nombre_comercial;
    
    public int Id {
      get { return id; }
    }

    public string NombreComercial {
      get { return nombre_comercial; }
    }

    private static Centro CreateFromReader(SqlDataReader reader) {
      Centro centro = new Centro();

      centro.id = reader.GetInt32(0);
      centro.nombre_comercial = reader.GetString(1);

      return centro;
    }

    public static List<Centro> FindAll() {
      string query = "SELECT * FROM centros ORDER BY nombre_comercial";
      List<Centro> centros = new List<Centro>();
      
      SqlDataReader reader = DatabaseHelper.ExecuteReader(query);

      while (reader.Read()) {
        centros.Add(CreateFromReader(reader));
      }
      
      return centros;
    }

    public static List<Centro> FindByEjecutivo(int ejecutivo_id) {
      string query = "SELECT * FROM centros WHERE (ejecutivo_id=@ejecutivo_id) ORDER BY nombre_comercial";
      string connection_string = DatabaseHelper.GetConnectionString();
      SqlDataReader reader;
      List<Centro> centros = new List<Centro>();
      
      using (SqlConnection connection = new SqlConnection(connection_string)) {
        connection.Open();
        
        SqlCommand command = new SqlCommand(query, connection);
        command.CommandType = CommandType.Text;
        
        command.Parameters.Add("@ejecutivo_id", SqlDbType.Int).Value = ejecutivo_id;
        
        try {
          reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch {
          connection.Close();
          throw;
        }
        
        while(reader.Read()) {
          centros.Add(CreateFromReader(reader));
        }
      }

      return centros;
    }
  }
}
